<!doctype html>



  


<html class="theme-next mist use-motion">
<head>
  <meta charset="UTF-8"/>
<meta http-equiv="X-UA-Compatible" content="IE=edge,chrome=1" />
<meta name="viewport" content="width=device-width, initial-scale=1, maximum-scale=1"/>



<meta http-equiv="Cache-Control" content="no-transform" />
<meta http-equiv="Cache-Control" content="no-siteapp" />












  <link href="/vendors/fancybox/source/jquery.fancybox.css?v=2.1.5" rel="stylesheet" type="text/css"/>




  <link href="//fonts.googleapis.com/css?family=Lato:300,400,700,400italic&subset=latin,latin-ext" rel="stylesheet" type="text/css">



<link href="/vendors/font-awesome/css/font-awesome.min.css?v=4.4.0" rel="stylesheet" type="text/css" />

<link href="/css/main.css?v=0.5.0" rel="stylesheet" type="text/css" />


  <meta name="keywords" content="Hexo, NexT" />





  <link rel="alternate" href="/atom.xml" title="Joshua Shaw之秘密花园" type="application/atom+xml" />




  <link rel="shortcut icon" type="image/x-icon" href="/favicon.ico?v=0.5.0" />






<meta name="description">
<meta property="og:type" content="website">
<meta property="og:title" content="Joshua Shaw之秘密花园">
<meta property="og:url" content="http://joshshaw.github.io/page/2/index.html">
<meta property="og:site_name" content="Joshua Shaw之秘密花园">
<meta property="og:description">
<meta name="twitter:card" content="summary">
<meta name="twitter:title" content="Joshua Shaw之秘密花园">
<meta name="twitter:description">



<script type="text/javascript" id="hexo.configuration">
  var NexT = window.NexT || {};
  var CONFIG = {
    scheme: 'Mist',
    sidebar: {"position":"left","display":"always"},
    fancybox: true,
    motion: true,
    duoshuo: {
      userId: 0,
      author: '博主'
    }
  };
</script>

  <title> Joshua Shaw之秘密花园 </title>
</head>

<body itemscope itemtype="http://schema.org/WebPage" lang="zh-Hans">

  



  <script type="text/javascript">
    var _hmt = _hmt || [];
    (function() {
      var hm = document.createElement("script");
      hm.src = "//hm.baidu.com/hm.js?99d95abecc387f2aa83d123804dddd4c";
      var s = document.getElementsByTagName("script")[0];
      s.parentNode.insertBefore(hm, s);
    })();
  </script>








  
  
    
  

  <div class="container one-collumn sidebar-position-left 
   page-home 
 ">
    <div class="headband"></div>

    <header id="header" class="header" itemscope itemtype="http://schema.org/WPHeader">
      <div class="header-inner"><div class="site-meta ">
  

  <div class="custom-logo-site-title">
    <a href="/"  class="brand" rel="start">
      <span class="logo-line-before"><i></i></span>
      <span class="site-title">Joshua Shaw之秘密花园</span>
      <span class="logo-line-after"><i></i></span>
    </a>
  </div>
  <p class="site-subtitle">成就自己</p>
</div>

<div class="site-nav-toggle">
  <button>
    <span class="btn-bar"></span>
    <span class="btn-bar"></span>
    <span class="btn-bar"></span>
  </button>
</div>

<nav class="site-nav">
  

  
    <ul id="menu" class="menu">
      
        
        <li class="menu-item menu-item-home">
          <a href="/" rel="section">
            
              <i class="menu-item-icon fa fa-home fa-fw"></i> <br />
            
            首页
          </a>
        </li>
      
        
        <li class="menu-item menu-item-about">
          <a href="/about" rel="section">
            
              <i class="menu-item-icon fa fa-user fa-fw"></i> <br />
            
            关于
          </a>
        </li>
      
        
        <li class="menu-item menu-item-categories">
          <a href="/categories" rel="section">
            
              <i class="menu-item-icon fa fa-th fa-fw"></i> <br />
            
            分类
          </a>
        </li>
      
        
        <li class="menu-item menu-item-archives">
          <a href="/archives" rel="section">
            
              <i class="menu-item-icon fa fa-archive fa-fw"></i> <br />
            
            归档
          </a>
        </li>
      
        
        <li class="menu-item menu-item-tags">
          <a href="/tags" rel="section">
            
              <i class="menu-item-icon fa fa-tags fa-fw"></i> <br />
            
            标签
          </a>
        </li>
      

      
    </ul>
  

  
</nav>

 </div>
    </header>

    <main id="main" class="main">
      <div class="main-inner">
        <div class="content-wrap">
          <div id="content" class="content">
            
  <section id="posts" class="posts-expand">
    
      

  
  

  
  
  

  <article class="post post-type-normal " itemscope itemtype="http://schema.org/Article">

    
      <header class="post-header">

        
        
          <h1 class="post-title" itemprop="name headline">
            
            
              
                
                <a class="post-title-link" href="/2016/05/04/《高性能MySQL》指南摘要/" itemprop="url">
                  《高性能MySQL》指南摘要
                </a>
              
            
          </h1>
        

        <div class="post-meta">
          <span class="post-time">
            <span class="post-meta-item-icon">
              <i class="fa fa-calendar-o"></i>
            </span>
            <span class="post-meta-item-text">发表于</span>
            <time itemprop="dateCreated" datetime="2016-05-04T16:19:15+08:00" content="2016-05-04">
              2016-05-04
            </time>
          </span>

          
            <span class="post-category" >
              &nbsp; | &nbsp;
              <span class="post-meta-item-icon">
                <i class="fa fa-folder-o"></i>
              </span>
              <span class="post-meta-item-text">分类于</span>
              
                <span itemprop="about" itemscope itemtype="https://schema.org/Thing">
                  <a href="/categories/汇总/" itemprop="url" rel="index">
                    <span itemprop="name">汇总</span>
                  </a>
                </span>

                
                

              
            </span>
          

          
            
              <span class="post-comments-count">
                &nbsp; | &nbsp;
                <a href="/2016/05/04/《高性能MySQL》指南摘要/#comments" itemprop="discussionUrl">
                  <span class="post-comments-count ds-thread-count" data-thread-key="2016/05/04/《高性能MySQL》指南摘要/" itemprop="commentsCount"></span>
                </a>
              </span>
            
          

          

          
          

        </div>
      </header>
    


    <div class="post-body" itemprop="articleBody">

      
      

      
        
          
            <p>《高性能MySQL》一书给我们做了许多指导，其中有5章应该精读的：</p>
<ul>
<li><a href="http://joshshaw.github.io/2016/04/28/%E3%80%8A%E9%AB%98%E6%80%A7%E8%83%BDMySQL%E3%80%8B%E3%81%AESchema%E4%B8%8E%E6%95%B0%E6%8D%AE%E7%B1%BB%E5%9E%8B%E4%BC%98%E5%8C%96/">Schema与数据类型优化</a></li>
<li><a href="http://joshshaw.github.io/2016/04/29/%E3%80%8A%E9%AB%98%E6%80%A7%E8%83%BDMySQL%E3%80%8B%E3%81%AE%E5%88%9B%E5%BB%BA%E9%AB%98%E6%80%A7%E8%83%BD%E7%9A%84%E7%B4%A2%E5%BC%95/">创建高性能的索引</a></li>
<li><a href="http://joshshaw.github.io/2016/05/04/%E3%80%8A%E9%AB%98%E6%80%A7%E8%83%BDMySQL%E3%80%8B%E3%81%AE%E6%9F%A5%E8%AF%A2%E6%80%A7%E8%83%BD%E4%BC%98%E5%8C%96/">查询性能优化</a></li>
<li><a href="http://joshshaw.github.io/2016/04/22/%E3%80%8A%E9%AB%98%E6%80%A7%E8%83%BDMySQL%E3%80%8B%E3%81%AEMySQL%E9%AB%98%E7%BA%A7%E7%89%B9%E6%80%A7/">MySQL高级特性</a></li>
<li><a href="http://joshshaw.github.io/2016/04/26/%E3%80%8A%E9%AB%98%E6%80%A7%E8%83%BDMySQL%E3%80%8B%E3%81%AE%E5%A4%8D%E5%88%B6/">复制</a></li>
</ul>
<p>我对这几章都读过不下三遍了，有一些理解，当然更多的是疑惑，顺便在阅读的时候做了一些关键的摘要，方便以后查阅，当然在这里也共享给大家阅读一下。</p>
<p>可能我的总结有纰漏，请大家评判指正，谢谢。</p>

          
        
      
    </div>

    <div>
      
    </div>

    <footer class="post-footer">
      

      

      
      
        <div class="post-eof"></div>
      
    </footer>
  </article>


    
      

  
  

  
  
  

  <article class="post post-type-normal " itemscope itemtype="http://schema.org/Article">

    
      <header class="post-header">

        
        
          <h1 class="post-title" itemprop="name headline">
            
            
              
                
                <a class="post-title-link" href="/2016/05/04/《高性能MySQL》の查询性能优化/" itemprop="url">
                  《高性能MySQL》の查询性能优化
                </a>
              
            
          </h1>
        

        <div class="post-meta">
          <span class="post-time">
            <span class="post-meta-item-icon">
              <i class="fa fa-calendar-o"></i>
            </span>
            <span class="post-meta-item-text">发表于</span>
            <time itemprop="dateCreated" datetime="2016-05-04T15:07:23+08:00" content="2016-05-04">
              2016-05-04
            </time>
          </span>

          
            <span class="post-category" >
              &nbsp; | &nbsp;
              <span class="post-meta-item-icon">
                <i class="fa fa-folder-o"></i>
              </span>
              <span class="post-meta-item-text">分类于</span>
              
                <span itemprop="about" itemscope itemtype="https://schema.org/Thing">
                  <a href="/categories/计算机科学/" itemprop="url" rel="index">
                    <span itemprop="name">计算机科学</span>
                  </a>
                </span>

                
                

              
            </span>
          

          
            
              <span class="post-comments-count">
                &nbsp; | &nbsp;
                <a href="/2016/05/04/《高性能MySQL》の查询性能优化/#comments" itemprop="discussionUrl">
                  <span class="post-comments-count ds-thread-count" data-thread-key="2016/05/04/《高性能MySQL》の查询性能优化/" itemprop="commentsCount"></span>
                </a>
              </span>
            
          

          

          
          

        </div>
      </header>
    


    <div class="post-body" itemprop="articleBody">

      
      

      
        
          <h2 id="0x00前言"><a href="#0x00前言" class="headerlink" title="0x00前言"></a>0x00前言</h2><p>本书讲述到定稿前的MySQL5.5版，所以下面内容的适用范围止步于MySQL5.5。本文仅仅强调书中讲述的重中之重，<br>以便快速查阅，详细的内容还请认真阅读书本和MySQL的官方文档。</p>
<hr>
<h2 id="0x01-为什么查询速度会慢"><a href="#0x01-为什么查询速度会慢" class="headerlink" title="0x01 为什么查询速度会慢"></a>0x01 为什么查询速度会慢</h2><p>查询性能低下的最基本的原因是访问的数据太多。</p>
<ul>
<li>查询不需要的记录。使用LIMIT来限制。</li>
<li>多表关联时返回全部列。</li>
<li>总是取出全部列。可简化开发或者有利于某种缓存机制。</li>
<li>重复查询相同的数据。</li>
</ul>
<p><strong>响应时间：包括服务时间和排队时间，排队时间最常见和重要的是IO和锁等待。</strong><br><strong>快速上限估计：获得一个大概参考值来判断当前响应时间是不是一个合理的值。</strong><br>扫描后返回行数和访问类型：</p>
<ol>
<li>做一个关联查询时，服务器必须要扫描多行才能生成结果集中的一行。扫描的行数对返回的行数的比率通常很小，<br>如果在1:1到10:1之间是比较好的。</li>
<li>全表扫描 &lt; 索引扫描 &lt; 范围扫描 &lt; 唯一索引查询 &lt; 常数引用。优秀的索引能极大地提高扫描的速度。</li>
</ol>
<p>应用WHERE条件时从好到坏依次为：</p>
<ol>
<li>在索引中过滤不匹配的记录，在存储引擎层完成。</li>
<li>使用索引覆盖扫描，在MySQL服务器层完成，无需回到数据表进行查询。</li>
<li>从数据表返回数据，MySQL需要先从数据表读出记录然后过滤，在MySQL服务器层完成。</li>
</ol>
<p><strong>因此，不需要的数据应该尽可能地从越底层被过滤，如果数据还要提升到服务器层（而不是在存储引擎层）进行过滤<br>那么就会对性能有影响。</strong></p>
<p>如果发现查询需要扫描大量的数据但是只返回少数的行，优化技巧如下：</p>
<ul>
<li>使用索引覆盖扫描</li>
<li>使用单独的汇总表</li>
<li>重写查询语句</li>
</ul>
<hr>
          <div class="post-more-link text-center">
            <a class="btn" href="/2016/05/04/《高性能MySQL》の查询性能优化/#more" rel="contents">
              阅读全文 &raquo;
            </a>
          </div>
        
      
    </div>

    <div>
      
    </div>

    <footer class="post-footer">
      

      

      
      
        <div class="post-eof"></div>
      
    </footer>
  </article>


    
      

  
  

  
  
  

  <article class="post post-type-normal " itemscope itemtype="http://schema.org/Article">

    
      <header class="post-header">

        
        
          <h1 class="post-title" itemprop="name headline">
            
            
              
                
                <a class="post-title-link" href="/2016/04/29/《高性能MySQL》の创建高性能的索引/" itemprop="url">
                  《高性能MySQL》の创建高性能的索引
                </a>
              
            
          </h1>
        

        <div class="post-meta">
          <span class="post-time">
            <span class="post-meta-item-icon">
              <i class="fa fa-calendar-o"></i>
            </span>
            <span class="post-meta-item-text">发表于</span>
            <time itemprop="dateCreated" datetime="2016-04-29T16:07:50+08:00" content="2016-04-29">
              2016-04-29
            </time>
          </span>

          
            <span class="post-category" >
              &nbsp; | &nbsp;
              <span class="post-meta-item-icon">
                <i class="fa fa-folder-o"></i>
              </span>
              <span class="post-meta-item-text">分类于</span>
              
                <span itemprop="about" itemscope itemtype="https://schema.org/Thing">
                  <a href="/categories/计算机科学/" itemprop="url" rel="index">
                    <span itemprop="name">计算机科学</span>
                  </a>
                </span>

                
                

              
            </span>
          

          
            
              <span class="post-comments-count">
                &nbsp; | &nbsp;
                <a href="/2016/04/29/《高性能MySQL》の创建高性能的索引/#comments" itemprop="discussionUrl">
                  <span class="post-comments-count ds-thread-count" data-thread-key="2016/04/29/《高性能MySQL》の创建高性能的索引/" itemprop="commentsCount"></span>
                </a>
              </span>
            
          

          

          
          

        </div>
      </header>
    


    <div class="post-body" itemprop="articleBody">

      
      

      
        
          <h2 id="0x00前言"><a href="#0x00前言" class="headerlink" title="0x00前言"></a>0x00前言</h2><p>本书讲述到定稿前的MySQL5.5版，所以下面内容的适用范围止步于MySQL5.5。本文仅仅强调书中讲述的重中之重，<br>以便快速查阅，详细的内容还请认真阅读书本和MySQL的官方文档。</p>
<hr>
<h2 id="0x01索引基础"><a href="#0x01索引基础" class="headerlink" title="0x01索引基础"></a>0x01索引基础</h2><p>索引可以包含一个或多个列的值。</p>
<h3 id="B-Tree索引"><a href="#B-Tree索引" class="headerlink" title="B-Tree索引"></a>B-Tree索引</h3><ol>
<li>简介<br>实际上很多存储引擎使用的是B+Tree（如下图）。<ul>
<li>每一个叶子节点都包含指向下一个叶子节点的指针，从而方便叶子节点的范围遍历。</li>
<li>指向下层子节点的指针实际上定义了子节点页中值的上限和下限。</li>
<li>叶子节点的指针指向的是被索引的数据。<img src="/2016/04/29/《高性能MySQL》の创建高性能的索引/b+tree.jpg" alt="b+tree.jpg" title="">
（文章最后有B树和B+树的一些简介）</li>
</ul>
</li>
<li>可以使用B-Tree索引的查询类型<ul>
<li>全值匹配</li>
<li>匹配最左前缀</li>
<li>匹配列前缀</li>
<li>匹配范围值</li>
<li>精确匹配某一列并范围匹配另外一列</li>
<li>只访问索引的查询</li>
</ul>
</li>
<li>B-Tree索引的限制<br>这些限制都和索引列的顺序有关。<ul>
<li>不能从索引的中间列开始查找</li>
<li>不能跳过索引中的列</li>
<li>若查询中有某个列的范围查询，则右边所有列都无法使用索引来优化查询</li>
</ul>
</li>
</ol>
          <div class="post-more-link text-center">
            <a class="btn" href="/2016/04/29/《高性能MySQL》の创建高性能的索引/#more" rel="contents">
              阅读全文 &raquo;
            </a>
          </div>
        
      
    </div>

    <div>
      
    </div>

    <footer class="post-footer">
      

      

      
      
        <div class="post-eof"></div>
      
    </footer>
  </article>


    
      

  
  

  
  
  

  <article class="post post-type-normal " itemscope itemtype="http://schema.org/Article">

    
      <header class="post-header">

        
        
          <h1 class="post-title" itemprop="name headline">
            
            
              
                
                <a class="post-title-link" href="/2016/04/28/《高性能MySQL》のSchema与数据类型优化/" itemprop="url">
                  《高性能MySQL》のSchema与数据类型优化
                </a>
              
            
          </h1>
        

        <div class="post-meta">
          <span class="post-time">
            <span class="post-meta-item-icon">
              <i class="fa fa-calendar-o"></i>
            </span>
            <span class="post-meta-item-text">发表于</span>
            <time itemprop="dateCreated" datetime="2016-04-28T13:41:45+08:00" content="2016-04-28">
              2016-04-28
            </time>
          </span>

          
            <span class="post-category" >
              &nbsp; | &nbsp;
              <span class="post-meta-item-icon">
                <i class="fa fa-folder-o"></i>
              </span>
              <span class="post-meta-item-text">分类于</span>
              
                <span itemprop="about" itemscope itemtype="https://schema.org/Thing">
                  <a href="/categories/计算机科学/" itemprop="url" rel="index">
                    <span itemprop="name">计算机科学</span>
                  </a>
                </span>

                
                

              
            </span>
          

          
            
              <span class="post-comments-count">
                &nbsp; | &nbsp;
                <a href="/2016/04/28/《高性能MySQL》のSchema与数据类型优化/#comments" itemprop="discussionUrl">
                  <span class="post-comments-count ds-thread-count" data-thread-key="2016/04/28/《高性能MySQL》のSchema与数据类型优化/" itemprop="commentsCount"></span>
                </a>
              </span>
            
          

          

          
          

        </div>
      </header>
    


    <div class="post-body" itemprop="articleBody">

      
      

      
        
          <h2 id="0x00前言"><a href="#0x00前言" class="headerlink" title="0x00前言"></a>0x00前言</h2><p>本书讲述到定稿前的MySQL5.5版，所以下面内容的适用范围止步于MySQL5.5。本文仅仅强调书中讲述的重中之重，<br>以便快速查阅，详细的内容还请认真阅读书本和MySQL的官方文档。</p>
<hr>
<h2 id="0x01简介"><a href="#0x01简介" class="headerlink" title="0x01简介"></a>0x01简介</h2><p>良好的逻辑设计和物理设计是高性能的基石。Schema的设计是极为重要的，下面将会简单介绍具体情况下适合的设计。</p>
<hr>
<h2 id="0x02选择数据类型"><a href="#0x02选择数据类型" class="headerlink" title="0x02选择数据类型"></a>0x02选择数据类型</h2><ul>
<li>更小的通常更好。这样会占用更少的磁盘，内存和CPU</li>
<li>简单就好。日期用日期类型DATETIME、TIMESTAMP而不是字符串等等。</li>
<li>尽量避免NULL。如果该列上建立索引，那么应该尽量避免NULL，但是也不能全不用NULL。</li>
</ul>
<h3 id="整数类型"><a href="#整数类型" class="headerlink" title="整数类型"></a>整数类型</h3><table>
<thead>
<tr>
<th style="text-align:center">类型</th>
<th style="text-align:center">TINYINT</th>
<th style="text-align:center">SMALLINT</th>
<th style="text-align:center">MEDIUMINT</th>
<th style="text-align:center">INT</th>
<th style="text-align:center">BIGINT</th>
</tr>
</thead>
<tbody>
<tr>
<td style="text-align:center">比特</td>
<td style="text-align:center">8</td>
<td style="text-align:center">16</td>
<td style="text-align:center">24</td>
<td style="text-align:center">32</td>
<td style="text-align:center">64</td>
</tr>
</tbody>
</table>
<p>字段非负时最好用上UNSIGNED。标识符一般使用整数字段，并且金钱一般也使用整数来存储，只需要乘以响应的<br>位数，这样计算上不需要使用float或者double，计算速度更快，并且不失精确性。而IPv4地址也最好用整数存储。</p>
<h3 id="实数类型"><a href="#实数类型" class="headerlink" title="实数类型"></a>实数类型</h3><p>DECIMAL类型也能精确计算，但是代缴较高，能用整数代替时最好用整数。</p>
<h3 id="字符串类型"><a href="#字符串类型" class="headerlink" title="字符串类型"></a>字符串类型</h3><p>字符串类型用较多种，如VARCHAR、CHAR、TEXT、BOLB等。<br>数据库、表、和表中的列都可以定义自己的字符集和排序规则，但是一般情况下最好统一，这样能简化开发。<br>字符长度不要分配太多，应该分配适合范围内的最小值。<br>
          <div class="post-more-link text-center">
            <a class="btn" href="/2016/04/28/《高性能MySQL》のSchema与数据类型优化/#more" rel="contents">
              阅读全文 &raquo;
            </a>
          </div>
        
      
    </div>

    <div>
      
    </div>

    <footer class="post-footer">
      

      

      
      
        <div class="post-eof"></div>
      
    </footer>
  </article>


    
      

  
  

  
  
  

  <article class="post post-type-normal " itemscope itemtype="http://schema.org/Article">

    
      <header class="post-header">

        
        
          <h1 class="post-title" itemprop="name headline">
            
            
              
                
                <a class="post-title-link" href="/2016/04/26/《高性能MySQL》の复制/" itemprop="url">
                  《高性能MySQL》の复制
                </a>
              
            
          </h1>
        

        <div class="post-meta">
          <span class="post-time">
            <span class="post-meta-item-icon">
              <i class="fa fa-calendar-o"></i>
            </span>
            <span class="post-meta-item-text">发表于</span>
            <time itemprop="dateCreated" datetime="2016-04-26T19:19:20+08:00" content="2016-04-26">
              2016-04-26
            </time>
          </span>

          
            <span class="post-category" >
              &nbsp; | &nbsp;
              <span class="post-meta-item-icon">
                <i class="fa fa-folder-o"></i>
              </span>
              <span class="post-meta-item-text">分类于</span>
              
                <span itemprop="about" itemscope itemtype="https://schema.org/Thing">
                  <a href="/categories/计算机科学/" itemprop="url" rel="index">
                    <span itemprop="name">计算机科学</span>
                  </a>
                </span>

                
                

              
            </span>
          

          
            
              <span class="post-comments-count">
                &nbsp; | &nbsp;
                <a href="/2016/04/26/《高性能MySQL》の复制/#comments" itemprop="discussionUrl">
                  <span class="post-comments-count ds-thread-count" data-thread-key="2016/04/26/《高性能MySQL》の复制/" itemprop="commentsCount"></span>
                </a>
              </span>
            
          

          

          
          

        </div>
      </header>
    


    <div class="post-body" itemprop="articleBody">

      
      

      
        
          <h2 id="0x00前言"><a href="#0x00前言" class="headerlink" title="0x00前言"></a>0x00前言</h2><p>本书讲述到定稿前的MySQL5.5版，所以下面内容的适用范围止步于MySQL5.5。本文仅仅强调书中讲述的重中之重，<br>以便快速查阅，详细的内容还请认真阅读书本和MySQL的官方文档。</p>
<h2 id="0x01简介"><a href="#0x01简介" class="headerlink" title="0x01简介"></a>0x01简介</h2><p>本章阐述所有与复制相关的内容</p>
<ol>
<li>简要介绍复制如何工作</li>
<li>讨论基本的复制服务搭建<ul>
<li>与复制相关的配置</li>
<li>如何管理和优化复制服务器</li>
</ul>
</li>
</ol>
<hr>
<h2 id="0x02复制概述"><a href="#0x02复制概述" class="headerlink" title="0x02复制概述"></a>0x02复制概述</h2><ul>
<li>MySQL支持两种复制方式：基于行的复制和基于语句的复制。</li>
<li>都是通过主库上记录二进制日志，虽然有开销，但是不会很大。</li>
<li>同一时间点备库上的数据可能与主库存在不一致性，并无法保证主备之间的延迟。</li>
<li>通过复制可以将读操作指向备库来获得更好地读扩展。</li>
<li>目前备库只能串行化执行。</li>
</ul>
<h3 id="复制解决的问题"><a href="#复制解决的问题" class="headerlink" title="复制解决的问题"></a>复制解决的问题</h3><ul>
<li>数据分布</li>
<li>负载均衡</li>
<li>备份</li>
<li>高可用性和故障切换</li>
<li>MySQL升级</li>
</ul>
<h3 id="复制如何工作"><a href="#复制如何工作" class="headerlink" title="复制如何工作"></a>复制如何工作</h3><ul>
<li>主库把数据更改记录到二进制日志中。</li>
<li>备库将主库上的日志复制到自己的中继日志中。</li>
<li>备库读取中继日志中的事件，将其重放到备库数据之上。</li>
</ul>
          <div class="post-more-link text-center">
            <a class="btn" href="/2016/04/26/《高性能MySQL》の复制/#more" rel="contents">
              阅读全文 &raquo;
            </a>
          </div>
        
      
    </div>

    <div>
      
    </div>

    <footer class="post-footer">
      

      

      
      
        <div class="post-eof"></div>
      
    </footer>
  </article>


    
  </section>

  
  <nav class="pagination">
    <a class="extend prev" rel="prev" href="/"><i class="fa fa-angle-left"></i></a><a class="page-number" href="/">1</a><span class="page-number current">2</span><a class="page-number" href="/page/3/">3</a><a class="page-number" href="/page/4/">4</a><a class="extend next" rel="next" href="/page/3/"><i class="fa fa-angle-right"></i></a>
  </nav>



          </div>
          


          

        </div>
        
          
  
  <div class="sidebar-toggle">
    <div class="sidebar-toggle-line-wrap">
      <span class="sidebar-toggle-line sidebar-toggle-line-first"></span>
      <span class="sidebar-toggle-line sidebar-toggle-line-middle"></span>
      <span class="sidebar-toggle-line sidebar-toggle-line-last"></span>
    </div>
  </div>

  <aside id="sidebar" class="sidebar">
    <div class="sidebar-inner">

      

      

      <section class="site-overview sidebar-panel  sidebar-panel-active ">
        <div class="site-author motion-element" itemprop="author" itemscope itemtype="http://schema.org/Person">
          <img class="site-author-image" itemprop="image"
               src="/images/avatar.png"
               alt="Joshua Shaw" />
          <p class="site-author-name" itemprop="name">Joshua Shaw</p>
          <p class="site-description motion-element" itemprop="description"></p>
        </div>
        <nav class="site-state motion-element">
          <div class="site-state-item site-state-posts">
            <a href="/archives">
              <span class="site-state-item-count">20</span>
              <span class="site-state-item-name">日志</span>
            </a>
          </div>

          
            <div class="site-state-item site-state-categories">
              <a href="/categories">
                <span class="site-state-item-count">6</span>
                <span class="site-state-item-name">分类</span>
              </a>
            </div>
          

          
            <div class="site-state-item site-state-tags">
              <a href="/tags">
                <span class="site-state-item-count">26</span>
                <span class="site-state-item-name">标签</span>
              </a>
            </div>
          

        </nav>

        
          <div class="feed-link motion-element">
            <a href="/atom.xml" rel="alternate">
              <i class="fa fa-rss"></i>
              RSS
            </a>
          </div>
        

        <div class="links-of-author motion-element">
          
            
              <span class="links-of-author-item">
                <a href="https://github.com/joshuashawnetlab" target="_blank">
                  
                    <i class="fa fa-globe"></i> Github_1
                  
                </a>
              </span>
            
              <span class="links-of-author-item">
                <a href="https://github.com/joshshaw" target="_blank">
                  
                    <i class="fa fa-globe"></i> Github_2
                  
                </a>
              </span>
            
              <span class="links-of-author-item">
                <a href="http://weibo.com/u/2622463195" target="_blank">
                  
                    <i class="fa fa-weibo"></i> Weibo
                  
                </a>
              </span>
            
              <span class="links-of-author-item">
                <a href="http://my.oschina.net/joshuashaw" target="_blank">
                  
                    <i class="fa fa-globe"></i> OSChina
                  
                </a>
              </span>
            
              <span class="links-of-author-item">
                <a href="https://www.zhihu.com/people/xiao-chong-chong-41" target="_blank">
                  
                    <i class="fa fa-globe"></i> Zhihu
                  
                </a>
              </span>
            
          
        </div>

        
        

        
        <div class="links-of-blogroll motion-element">
          
        </div>

      </section>

      

    </div>
  </aside>


        
      </div>
    </main>

    <footer id="footer" class="footer">
      <div class="footer-inner">
        <div class="copyright" >
  
  &copy;  2015 - 
  <span itemprop="copyrightYear">2016</span>
  <span class="with-love">
    <i class="fa fa-heart"></i>
  </span>
  <span class="author" itemprop="copyrightHolder">Joshua Shaw</span>
</div>

<div class="powered-by">
  由 <a class="theme-link" href="http://hexo.io">Hexo</a> 强力驱动
</div>

<div class="theme-info">
  主题 -
  <a class="theme-link" href="https://github.com/iissnan/hexo-theme-next">
    NexT.Mist
  </a>
</div>



      </div>
    </footer>

    <div class="back-to-top">
      <i class="fa fa-arrow-up"></i>
    </div>
  </div>

  


  




<script type="text/javascript">
  if (Object.prototype.toString.call(window.Promise) !== '[object Function]') {
    window.Promise = null;
  }
</script>


  <script type="text/javascript" src="/vendors/jquery/index.js?v=2.1.3"></script>

  <script type="text/javascript" src="/vendors/fastclick/lib/fastclick.min.js?v=1.0.6"></script>

  <script type="text/javascript" src="/vendors/jquery_lazyload/jquery.lazyload.js?v=1.9.7"></script>

  <script type="text/javascript" src="/vendors/velocity/velocity.min.js"></script>

  <script type="text/javascript" src="/vendors/velocity/velocity.ui.min.js"></script>

  <script type="text/javascript" src="/vendors/fancybox/source/jquery.fancybox.pack.js"></script>


  


  <script type="text/javascript" src="/js/src/utils.js?v=0.5.0"></script>

  <script type="text/javascript" src="/js/src/motion.js?v=0.5.0"></script>



  
  

  

  


  <script type="text/javascript" src="/js/src/bootstrap.js?v=0.5.0"></script>



  

  
    
  

  <script type="text/javascript">
    var duoshuoQuery = {short_name:"joshuashaw"};
    (function() {
      var ds = document.createElement('script');
      ds.type = 'text/javascript';ds.async = true;
      ds.id = 'duoshuo-script';
      ds.src = (document.location.protocol == 'https:' ? 'https:' : 'http:') + '//static.duoshuo.com/embed.js';
      ds.charset = 'UTF-8';
      (document.getElementsByTagName('head')[0]
      || document.getElementsByTagName('body')[0]).appendChild(ds);
    })();
  </script>

  
    
  





  
  
  

  


</body>
</html>
